﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using Workbench.Models;

namespace Workbench.Persistance
{
    public class BuildRepository : RepositoryBase, IDisposable
    { 
        public BuildRepository(Func<SQLiteConnection> connectionProvider) : base(connectionProvider) 
        {
            connection = connectionProvider();
        }

        #region Get methods

        public ProjectFileModel GetFileById(long rowId)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"SELECT RowId, Name, ParentDirectoryId, FileContentId, ContextIncluded FROM Files WHERE RowId = @RowId";
                cmd.Parameters.AddWithValue("@RowId", rowId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var model = new ProjectFileModel();
                        model.RowId = reader.GetInt64(0);

                        if (!reader.IsDBNull(1)) model.Name = reader.GetString(1);
                        if (!reader.IsDBNull(2)) model.ParentDirectoryId = reader.GetInt64(2);
                        if (!reader.IsDBNull(3)) model.FileContentId = reader.GetInt64(3);
                        if (!reader.IsDBNull(4)) model.ContextIncluded = reader.GetInt64(4) == 1;
                        return model;
                    }
                }
            }
            return null;
        }

        public ProjectDirectoryModel GetDirectoryById(long rowId)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"SELECT RowId, Name, ParentDirectoryId FROM Directories WHERE RowId = @RowId";
                cmd.Parameters.AddWithValue("@RowId", rowId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var model = new ProjectDirectoryModel();
                        model.RowId = reader.GetInt64(0);

                        if (!reader.IsDBNull(1)) model.Name = reader.GetString(1);
                        if (!reader.IsDBNull(2)) model.ParentDirectoryId = reader.GetInt64(2);
                        return model;
                    }
                }
            }
            return null;
        }

        public FileContentModel GetFileContentById(long rowId)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"SELECT Content FROM FileContents WHERE RowId = @RowId";
                cmd.Parameters.AddWithValue("@RowId", rowId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var model = new FileContentModel();
                        model.RowId = rowId;

                        if (!reader.IsDBNull(0)) model.Content = reader.GetString(0);
                        return model;
                    }
                }
            }
            return null;
        }

        public FileStatisticsModel GetFileStatisticsByFileId(long fileId)
        {
            using (var cmd = connectionProvider().CreateCommand())
            {
                cmd.CommandText = @"SELECT RowId, Paragraphs,Sentences,TextLengthInRunningWords,DifferentForms,MaximumNumberOfSentencesInParagraph,MaximumNumberOfWordsInSentence FROM FileStatistics WHERE FileId = @FileId";
                cmd.Parameters.AddWithValue("@FileId", fileId);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var model = new FileStatisticsModel();
                        model.RowId = reader.GetInt64(0);

                        if (!reader.IsDBNull(1)) model.Paragraphs = reader.GetInt64(1);
                        if (!reader.IsDBNull(2)) model.Sentences = reader.GetInt64(2);
                        if (!reader.IsDBNull(3)) model.TextLengthInRunningWords = reader.GetInt64(3);
                        if (!reader.IsDBNull(4)) model.DifferentForms = reader.GetInt64(4);
                        if (!reader.IsDBNull(5)) model.MaximumNumberOfSentencesInParagraph = reader.GetInt64(5);
                        if (!reader.IsDBNull(6)) model.MaximumNumberOfWordsInSentence = reader.GetInt64(6);

                        model.FileId = fileId;
                        return model;
                    }
                }
            }
            return null;
        }

        public ProjectDirectoryModel GetRootDirectoryModel()
        {
            var files = new Dictionary<long, ProjectFileModel>();
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"SELECT RowId, Name, ParentDirectoryId, FileContentId, ContextIncluded FROM Files";
                if(transaction != null) cmd.Transaction = transaction;
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var result = new ProjectFileModel();
                        result.RowId = reader.GetInt64(0);

                        if (!reader.IsDBNull(1)) result.Name = reader.GetString(1);
                        if (!reader.IsDBNull(2)) result.ParentDirectoryId = reader.GetInt64(2);
                        if (!reader.IsDBNull(3)) result.FileContentId = reader.GetInt64(3);
                        if (!reader.IsDBNull(4)) result.ContextIncluded = reader.GetInt64(4) == 1;


                        files.Add(result.RowId, result);
                    }
                }
            }

            var directories = new Dictionary<long, ProjectDirectoryModel>();
            ProjectDirectoryModel rootNode = null;
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = @"SELECT RowId, Name, ParentDirectoryid FROM Directories";
                if(transaction != null) cmd.Transaction = transaction;
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var result = new ProjectDirectoryModel();
                        result.RowId = reader.GetInt64(0);

                        if (!reader.IsDBNull(1)) result.Name = reader.GetString(1);
                        if (!reader.IsDBNull(2)) result.ParentDirectoryId = reader.GetInt64(2); else rootNode = result;

                        directories.Add(result.RowId, result);
                    }
                }
            }
            if (rootNode == null)
            {
                throw new Exception("Project root directory is missing");
            }

            foreach (var file in files.Values)
            {
                ProjectDirectoryModel parentDirectory;
                if (!directories.TryGetValue(file.ParentDirectoryId, out parentDirectory))
                {
                    var message = string.Format("There is orphan file in the database with rowid: {0}", file.ParentDirectoryId);
                    throw new WorkbenchDALException(message);
                }
                parentDirectory.Children.Add(file);
                file.ParentDirectory = parentDirectory;
            }

            foreach (var directory in directories.Values)
            {
                if (directory == rootNode)
                {
                    continue;
                }
                ProjectDirectoryModel parentDirectory;
                if (directories.TryGetValue(directory.ParentDirectoryId, out parentDirectory))
                {
                    parentDirectory.Children.Add(directory);
                    directory.ParentDirectory = parentDirectory;
                }
            }

            return rootNode;
        }

        public List<POSTypeModel> GetPOSTypesWithStatistics(IEnumerable<ProjectFileModel> filesInContext)
        {
            var posTypesById = new Dictionary<long, POSTypeModel>();
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = string.Format(
@"SELECT 
    p.RowId, 
    p.Name, 
    p.ParentPOSTypeId, 
(SELECT COUNT(*) 
FROM Words_POSTypes AS wps
INNER JOIN Words AS w ON w.RowId = wps.WordId
INNER JOIN Sentences AS s ON s.RowId = w.SentenceId
INNER JOIN Paragraphs AS par ON par.RowId = s.ParagraphId 
WHERE POSTypeId = p.RowId
AND par.FileId IN ({0})
) AS Count
FROM POSTypes 
AS p;", string.Join(", ", filesInContext.Select(x => x.RowId.ToString())));

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var result = new POSTypeModel();
                        result.RowId = reader.GetInt64(0);
                        if (!reader.IsDBNull(1)) result.Name = reader.GetString(1);
                        if (!reader.IsDBNull(2)) result.ParentPOSTypeId = reader.GetInt64(2);
                        if (!reader.IsDBNull(3)) result.Count = reader.GetInt64(3);

                        posTypesById.Add(result.RowId, result);
                    }
                }
            }

            var rootPOSTypes = new List<POSTypeModel>();
            foreach (var posType in posTypesById.Values)
            {
                POSTypeModel parentPOSType;
                if (!posTypesById.TryGetValue(posType.ParentPOSTypeId, out parentPOSType))
                {
                    rootPOSTypes.Add(posType);
                }
                else
                {
                    parentPOSType.Children.Add(posType);
                    posType.ParentPOSType = parentPOSType;
                }
            }

            return rootPOSTypes;
        }

        public List<POSTypeModel> GetPOSTypes()
        {
            var posTypesById = new Dictionary<long, POSTypeModel>();
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"SELECT p.RowId, p.Name, p.ParentPOSTypeId FROM POSTypes AS p;";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var result = new POSTypeModel();
                        result.RowId = reader.GetInt64(0);
                        if (!reader.IsDBNull(1)) result.Name = reader.GetString(1);
                        if (!reader.IsDBNull(2)) result.ParentPOSTypeId = reader.GetInt64(2);

                        posTypesById.Add(result.RowId, result);
                    }
                }
            }

            var rootPOSTypes = new List<POSTypeModel>();
            foreach (var posType in posTypesById.Values)
            {
                POSTypeModel parentPOSType;
                if (!posTypesById.TryGetValue(posType.ParentPOSTypeId, out parentPOSType))
                {
                    rootPOSTypes.Add(posType);
                }
                else
                {
                    parentPOSType.Children.Add(posType);
                    posType.ParentPOSType = parentPOSType;
                }
            }

            return rootPOSTypes;
        }

        public QuantitativeMeasures GetQuantitativeMeasures(IEnumerable<ProjectFileModel> contextFiles)
        {
#if DEBUG
            var stopwatch = new Stopwatch();
#endif
            var con = connectionProvider();
            var result = new QuantitativeMeasures();
            var fileIds = string.Join(",", contextFiles.Select(x => x.RowId.ToString()));
            result.TextLengthInRunningWords = DbUtility.ExecuteScalar<long>(con,
string.Format(@"SELECT SUM(fs.TextLengthInRunningWords) 
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("TextLengthInRunningWords: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif 
            result.Sentences = DbUtility.ExecuteScalar<long>(con,
string.Format(@"SELECT SUM(fs.Sentences) 
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("Sentences: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif
            result.Paragraphs = DbUtility.ExecuteScalar<long>(con,
string.Format(@"SELECT SUM(fs.Paragraphs) 
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("Paragraphs: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif

            result.DifferentForms = DbUtility.ExecuteScalar<long>(con,
string.Format(@"SELECT SUM(fs.DifferentForms) 
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("DifferentForms: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif

            result.MaximumNumberOfWordsInSentence = DbUtility.ExecuteScalar<long>(con,
string.Format(@"SELECT MAX(fs.MaximumNumberOfWordsInSentence) 
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("MaximumNumberOfWordsInSentence: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif

            result.MaximumNumberOfSentencesInParagraph = DbUtility.ExecuteScalar<long>(con,
string.Format(@"SELECT MAX(fs.MaximumNumberOfSentencesInParagraph) 
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("MaximumNumberOfSentencesInParagraph: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif

            result.AverageLengthOfSentenceInWords = DbUtility.ExecuteScalar<double>(con,
string.Format(@"SELECT SUM(fs.AverageLengthOfSentenceInWords * fs.Sentences) / SUM(fs.Sentences)
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("AverageLengthOfSentenceInWords: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif

            result.AverageLengthOfParagraphInWords = DbUtility.ExecuteScalar<double>(con,
string.Format(@"SELECT SUM(fs.AverageLengthOfParagraphInWords * fs.Paragraphs) / SUM(fs.Paragraphs)
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("AverageLengthOfParagraphInWords: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif

            result.AverageLengthOfParagraphInSentences = DbUtility.ExecuteScalar<double>(con,
string.Format(@"SELECT SUM(fs.AverageLengthOfParagraphInSentences * fs.Paragraphs) / SUM(fs.Paragraphs)
FROM FileStatistics fs
WHERE fs.FileId IN ({0});", fileIds));
#if DEBUG
            Trace.WriteLine(string.Format("AverageLengthOfParagraphInSentences: {0}ms", stopwatch.ElapsedMilliseconds));
            stopwatch.Restart();
#endif
            return result;
        }

        #endregion

        #region Delete methods

        public void DeletePOSType(POSTypeModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = "DELETE FROM POSTypes WHERE RowId = @RowId;";
                cmd.Parameters.AddWithValue("@RowId", model.RowId);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteProjectItem(ProjectItemModel model)
        {
            string tableName = model is ProjectFileModel ? "Files" : "Directories";
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText =  string.Format(@"DELETE FROM {0} WHERE RowId = @RowId;", tableName);
                cmd.Parameters.AddWithValue("@RowId", model.RowId);
                cmd.ExecuteNonQuery();
            }
        }

        #endregion Delete methods

        #region Persist methods

        public void PersistPOSType(POSTypeModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"INSERT INTO POSTypes (Name, ParentPOSTypeId) VALUES (@Name, @ParentPOSTypeId); SELECT last_insert_rowid();";
                cmd.Parameters.AddWithValue("@ParentPOSTypeId", DbUtility.GetForeignKeyValue(model.ParentPOSTypeId));
                cmd.Parameters.AddWithValue("@Name", model.Name);
                model.RowId = DbUtility.ExecuteScalar<long>(cmd);
                if (model.RowId == 0)
                {
                    var message = string.Format("Failed to persist POS type");
                    throw new Exception(message);
                }
            }
        }

        public void PersistFile(ProjectFileModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@ParentDirectoryId", model.ParentDirectoryId);

                if (model.FileContentId == 0)
                    model.FileContentId = model.FileContent.RowId;

                cmd.Parameters.AddWithValue("@FileContentId", DbUtility.GetForeignKeyValue(model.FileContentId));
                cmd.Parameters.AddWithValue("@ContextIncluded", model.ContextIncluded);
                if (model.RowId != 0)
                {
                    cmd.Parameters.AddWithValue("@RowId", model.RowId);
                    cmd.CommandText =
@"UPDATE Files 
SET Name = @Name, 
    ParentDirectoryId = @ParentDirectoryId, 
    FileContentId = @FileContentId,
    ContextIncluded = @ContextIncluded 
WHERE RowId = @RowId; SELECT changes()";
                    cmd.ExecuteNonQuery();
                    if (connection.Changes == 0)
                    {
                        var message = string.Format("File with id:{0} was not found in the database.", model.RowId);
                        throw new Exception(message);
                    }
                }
                else
                {
                    cmd.CommandText = 
@"INSERT INTO Files (Name, ParentDirectoryId, FileContentId, ContextIncluded) 
VALUES (@Name, @ParentDirectoryId, @FileContentId, @ContextIncluded);";
                    cmd.ExecuteNonQuery();
                    model.RowId = connection.LastInsertRowId;
                    if (model.RowId == 0)
                    {
                        var message = string.Format("Failed to persist file");
                        throw new Exception(message);
                    }
                }
            }
        }

        public void PersistFileContent(FileContentModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText = @"INSERT INTO FileContents (Content) VALUES (@Content);";
                cmd.Parameters.AddWithValue("@Content", model.Content);
                cmd.ExecuteNonQuery();
                model.RowId = connection.LastInsertRowId;
                if (model.RowId == 0)
                {
                    var message = string.Format("Failed to persist file content");
                    throw new Exception(message);
                }
            }
        }

        public void PersistProjectDirectory(ProjectDirectoryModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                if(transaction != null) cmd.Transaction = transaction;
                cmd.CommandText =
@"INSERT INTO Directories (Name, ParentDirectoryId) VALUES (@Name, @ParentDirectoryId);
SELECT last_insert_rowid();"; 
                cmd.Parameters.AddWithValue("@Name", model.Name);
                cmd.Parameters.AddWithValue("@ParentDirectoryId", DbUtility.GetForeignKeyValue(model.ParentDirectoryId));
                cmd.ExecuteNonQuery();
                model.RowId = connection.LastInsertRowId;
                if (model.RowId == 0)
                {
                    var message = string.Format("Failed to persist directory");
                    throw new Exception(message);
                }
            }
        }

        public void PersistParagraphs(IEnumerable<ParagraphModel> paragraphs)
        {
            using(var cmd = connection.CreateCommand())
            {
                var pStartPosition = new SQLiteParameter();
                var pLength = new SQLiteParameter();
                var pFileId = new SQLiteParameter();
                cmd.CommandText = "INSERT INTO Paragraphs (StartPosition, Length, FileId) VALUES(?, ?, ?)";
                cmd.Parameters.Add(pStartPosition);
                cmd.Parameters.Add(pLength);
                cmd.Parameters.Add(pFileId);

                foreach (var paragraph in paragraphs)
                {
                    pStartPosition.Value = paragraph.StartPosition;
                    pLength.Value = paragraph.Length;
                    pFileId.Value = paragraph.File.RowId;
                    cmd.ExecuteNonQuery();
                    paragraph.RowId = connection.LastInsertRowId;
                }
            }
        }

        public void PersistSentences(IEnumerable<SentenceModel> sentences)
        {
            using (var cmd = connection.CreateCommand())
            {
                var pStartPosition = new SQLiteParameter();
                var pLength = new SQLiteParameter();
                var pParagraphId = new SQLiteParameter();
                cmd.CommandText = "INSERT INTO Sentences (StartPosition, Length, ParagraphId) VALUES(?, ?, ?)";
                cmd.Parameters.Add(pStartPosition);
                cmd.Parameters.Add(pLength);
                cmd.Parameters.Add(pParagraphId);

                foreach (var sentence in sentences)
                {
                    pLength.Value = sentence.Length;
                    pStartPosition.Value = sentence.StartPosition;
                    pParagraphId.Value = sentence.Paragraph.RowId;
                    cmd.ExecuteNonQuery();
                    sentence.RowId = connection.LastInsertRowId;
                }
            }
        }

        public void PersistUniqueWords(IEnumerable<UniqueWordModel> uniqueWords)
        {
            using (var cmd = connection.CreateCommand())
            {
                var pContent = new SQLiteParameter();
                cmd.CommandText = "INSERT INTO UniqueWords (Content) VALUES(?)";
                cmd.Parameters.Add(pContent);

                foreach (var uniqueWord in uniqueWords)
                {
                    pContent.Value = uniqueWord.Content;
                    cmd.ExecuteNonQuery();
                    uniqueWord.RowId = connection.LastInsertRowId;
                }
            }
        }

        public void PersistWords(IEnumerable<WordModel> words)
        {
            using (var cmd = connection.CreateCommand())
            {
                var pUniqueWordId = new SQLiteParameter();
                var pSentenceId = new SQLiteParameter();
                cmd.CommandText = "INSERT INTO Words (SentenceId, UniqueWordId) VALUES(?, ?)";
                cmd.Parameters.Add(pSentenceId);
                cmd.Parameters.Add(pUniqueWordId);

                foreach (var word in words)
                {
                    pSentenceId.Value = word.Sentence.RowId;
                    pUniqueWordId.Value = word.UniqueWord.RowId;
                    cmd.ExecuteNonQuery();
                    word.RowId = connection.LastInsertRowId;
                }
            }
        }

        public void PersistFileStatistics(FileStatisticsModel model)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = 
@"INSERT INTO FileStatistics ("
+"FileId," 
+"Paragraphs,"
+"Sentences,"
+"TextLengthInRunningWords,"
+"DifferentForms,"
+"MaximumNumberOfSentencesInParagraph,"
+"MaximumNumberOfWordsInSentence,"
+"AverageLengthOfSentenceInWords,"
+"AverageLengthOfParagraphInWords,"
+"AverageLengthOfParagraphInSentences) VALUES ("
+"@FileId," 
+"@Paragraphs," 
+"@Sentences," 
+"@TextLengthInRunningWords," 
+"@DifferentForms," 
+"@MaximumNumberOfSentencesInParagraph," 
+"@MaximumNumberOfWordsInSentence,"
+"@AverageLengthOfSentenceInWords,"
+"@AverageLengthOfParagraphInWords,"
+"@AverageLengthOfParagraphInSentences);";
                var fileId = model.FileId == 0 ? model.File.RowId : model.FileId;
                cmd.Parameters.AddWithValue("@FileId", fileId);
                cmd.Parameters.AddWithValue("@Paragraphs", model.Paragraphs);
                cmd.Parameters.AddWithValue("@Sentences", model.Sentences);
                cmd.Parameters.AddWithValue("@TextLengthInRunningWords", model.TextLengthInRunningWords);
                cmd.Parameters.AddWithValue("@DifferentForms", model.DifferentForms);
                cmd.Parameters.AddWithValue("@MaximumNumberOfSentencesInParagraph", model.MaximumNumberOfSentencesInParagraph);
                cmd.Parameters.AddWithValue("@MaximumNumberOfWordsInSentence", model.MaximumNumberOfWordsInSentence);
                cmd.Parameters.AddWithValue("@AverageLengthOfSentenceInWords", model.AverageLengthOfSentenceInWords);
                cmd.Parameters.AddWithValue("@AverageLengthOfParagraphInWords", model.AverageLengthOfParagraphInWords);
                cmd.Parameters.AddWithValue("@AverageLengthOfParagraphInSentences", model.AverageLengthOfParagraphInSentences);
                cmd.ExecuteNonQuery();
                model.RowId = connection.LastInsertRowId;
                if (model.RowId == 0)
                {
                    var message = string.Format("Failed to persist file content");
                    throw new Exception(message);
                }
            }
        }

        #endregion Persist methods

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            if (disposing)
            {
                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
            }
        }
    }
}
